package com.etoak.util;

import com.etoak.common.config.MyConfig;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.utils.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

public class POIUtils {

    /**
     * Excel sheet最大行数，默认65536
     */
    public static final int sheetSize = 65536;
    /**
     * 公共样式
     */
    public static CellStyle headerCellStyle;
    public static CellStyle contentCellStyle;

    private static String[] parsePatterns = {"yyyy-MM-dd","yyyy年MM月dd日",
            "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm", "yyyy/MM/dd",
            "yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm", "yyyyMMdd"};

    /**
     * Excel 导出
     * @param header 表头
     * @param keys map的key值
     * @param content 内容数据
     * @param title 表格名字
     * @param sheetName sheet名
     */
    public static AjaxResult exportExcel(String[] header, String[] keys, List<Map<String, Object>> content, String title, String sheetName) throws Exception{
        String filename = encodingFilename(title);
        try(OutputStream out = new FileOutputStream(getAbsoluteFile(filename))) {
//            title = title + ".xlsx";
            Workbook wb = new SXSSFWorkbook(1000);
            headerCellStyle = headerStyle(wb);
            contentCellStyle = contentStyle(wb);

            fillExcelData(wb, header, keys, content, sheetName);
            wb.write(out);
            return AjaxResult.success(filename);
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * Excel 导出
     * @param response  HttpServletResponse
     * @param header 表头
     * @param keys map的key值
     * @param content 内容数据
     * @param title 表格名字
     * @param sheetName sheet名
     */
    public static void exportExcel(HttpServletResponse response, String[] header, String[] keys, List<Map<String, Object>> content, String title, String sheetName) {
        String filename = encodingFilename(title);
        try(OutputStream out = response.getOutputStream()) {
            Workbook wb = new SXSSFWorkbook(1000);
            headerCellStyle = headerStyle(wb);
            contentCellStyle = contentStyle(wb);

            fillExcelData(wb, header, keys, content, sheetName);

            response.reset();
            response.setContentType("application/octet-stream; charset=utf-8");
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Content-Disposition", "attachment; filename=" + filename);
            wb.write(out);
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 填充数据
     * @param wb
     * @param header
     * @param keys
     * @param content
     * @param sheetName
     */
    private static void fillExcelData(Workbook wb, String[] header, String[] keys, List<Map<String, Object>> content, String sheetName) {
        // 取出一共有多少个sheet.
        double sheetNo = Math.ceil(content.size() / sheetSize);
        for (int index = 0; index <= sheetNo; index++) {
            Sheet sheet = wb.createSheet();
            // 设置工作表的名称.
            if (sheetNo == 0) {
                wb.setSheetName(index, sheetName);
            } else {
                wb.setSheetName(index, sheetName + index);
            }
            Row row = sheet.createRow( 0);
            // 行高
            row.setHeight((short) 450);
            // 列宽
            for (int i = 0; i < header.length; i++) {
                if(header[i].contains("时间") || header[i].contains("日期")) {
                    sheet.setColumnWidth(i, 2 * 15 * 256);
                }else {
                    sheet.setColumnWidth(i, 15 * 256);
                }
            }
            for (int i = 0; i < header.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(header[i]);
                cell.setCellStyle(headerCellStyle);
            }
            int startNo = index * sheetSize;
            int endNo = Math.min(startNo + sheetSize, content.size());
            for (int i = startNo; i < endNo; i++) {
                Map<String, Object> map = content.get(i);
                row = sheet.createRow(i + 1 - startNo);
                row.setHeight((short) 400);
                for (int j = 0; j < keys.length; j++){
                    Cell cell = row.createCell(j);
                    if(map.get(keys[j]) instanceof Date) {
                        cell.setCellValue(map.get(keys[j]) == null ? "" : DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD_HH_MM_SS, (Date)map.get(keys[j])));
                    }else {
                        cell.setCellValue(map.get(keys[j]) == null ? "" : map.get(keys[j]).toString());
                    }
                    cell.setCellStyle(contentCellStyle);
                }
            }
        }
    }

    /**
     * 编码文件名
     */
    public static String encodingFilename(String filename) {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
        return filename;
    }

    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public static String getAbsoluteFile(String filename) {
        String downloadPath = MyConfig.getDownloadPath() + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists()) {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }

    /**
     * Excel 导入
     * @param file 文件
     * @param keys 数据顺序
     */
    public static  List<Map<String, Object>>  importExcel(MultipartFile file, String[] keys) throws Exception{
        Workbook wb = null;
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith("xls")) {
            POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
            wb = new HSSFWorkbook(pois);
        } else if (fileName.endsWith("xlsx")) {
            wb = new XSSFWorkbook(file.getInputStream());
        }
        Sheet sheet = wb.getSheetAt(0);
        int rowCount = sheet.getPhysicalNumberOfRows();
        if (sheet.getRow( 1).getPhysicalNumberOfCells() != keys.length){
            throw new RuntimeException("导入的Excel和模板的列不匹配");
        }
        List<Map<String,Object>> result = new ArrayList<>();
        for (int i = 0; i < rowCount - 1; i++) {
            Row row = sheet.getRow(i + 1);
            Map<String,Object> tmp = new HashMap<>();
            for (int j = 0;j < keys.length; j++){
                Cell cell = row.getCell(j);
                // 把类型转行Spring
//                cell.setCellType(CellType.STRING);
                tmp.put(keys[j], cell.getStringCellValue());
            }
            result.add(tmp);
        }
        return result;
    }

    /**
     * 表头样式
     */
    private static CellStyle headerStyle(Workbook wb){
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());
        CellStyle cellStyle = commonStyle(wb);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 内容样式
     */
    private static CellStyle contentStyle(Workbook wb){
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        CellStyle cellStyle = commonStyle(wb);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 公共样式
     */
    private static CellStyle commonStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setWrapText(true);// 自动换行
        return style;
    }

}
